unit DaftarkanTPS;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, Setting, ZAbstractTable, ZDataset, DB, ZAbstractRODataset,
  ZAbstractDataset, ZConnection;

type
  TForm_DaftarkanTPS = class(TForm)
    lblKet: TLabel;
    GroupBox1: TGroupBox;
    Label1: TLabel;
    Label2: TLabel;
    Label3: TLabel;
    Label4: TLabel;
    Label5: TLabel;
    lblJml: TLabel;
    btnDaftar: TButton;
    btnBatal: TButton;
    cbKabkot: TComboBox;
    cbKec: TComboBox;
    cbDesa: TComboBox;
    cbTPS: TComboBox;
    ZConnection1: TZConnection;
    ZQuery1: TZQuery;
    ZTable1: TZTable;
    procedure btnBatalClick(Sender: TObject);
    procedure btnDaftarClick(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure FormCreate(Sender: TObject);
    procedure cbKabkotChange(Sender: TObject);
    procedure cbKecChange(Sender: TObject);
    procedure cbDesaChange(Sender: TObject);
    procedure cbTPSChange(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
    function cekDaftar(id:String):Boolean;
    constructor Create(AOwner: TComponent; desa: String; pemilihs:TStringlist; kodelog:String); overload;
  end;

var
  Form_DaftarkanTPS: TForm_DaftarkanTPS;
  sdesa, skec, skabkot, stps, sjml, spemilih, skode, sdesaasal: String;
  vPem:TStringlist;

implementation

{$R *.dfm}

constructor TForm_DaftarkanTPS.Create(AOwner: TComponent; desa: String; pemilihs:TStringlist; kodelog:String);
begin
  sdesa := desa;
  sdesaasal := desa;
  vPem:= pemilihs;
  skode:=kodelog;
  inherited Create(AOwner);
end;

procedure TForm_DaftarkanTPS.FormClose
  (Sender: TObject; var Action: TCloseAction);
begin
  Action := caFree;
end;

procedure TForm_DaftarkanTPS.FormCreate(Sender: TObject);
var
  Setting: TSetting_Form;
  ntps, ikabkot, ikec, idesa, ndesa: String;
  i, idx: integer;
begin
  Setting := TSetting_Form.Create;
  ZConnection1.HostName := Setting.host;
  ZConnection1.Protocol := Setting.Protocol;
  ZConnection1.Database := Setting.Database;
  ZConnection1.User := Setting.User;
  ZConnection1.Password := Setting.Password;
  ZConnection1.LoginPrompt := False;
  ZConnection1.Connected := True;
  ZQuery1.Connection := ZConnection1;
  ZTable1.Connection := ZConnection1;
  // Keterangan
  WindowState:=wsNormal;
  with ZQuery1 do
  begin
    active := False;
    sql.Clear;
    sql.Text := 'SELECT * FROM desa WHERE KODE_DESA=' + QuotedStr(sdesa);
    active := True;
    first;
    idesa := sdesa;
    ndesa := FieldByName('TAMPIL').AsString;
    ikec := FieldByName('KODE_KECAMATAN').AsString;
    ikabkot := FieldByName('KODE_KABKOT').AsString;
  end;
  skec := ikec;
  skabkot := ikabkot;
  lblKet.Caption:='Anda akan mendaftarkan '+inttostr(vpem.Count)+' pemilih, dari Kel/Desa '+ndesa+' ke TPS : ';
  lblJml.caption:='';
  // KOTA
  with ZTable1 do
  begin
    active := False;
    TableName := 'kabkot';
    Filtered := False;
    active := True;
    first;
  end;
  cbKabkot.Clear;
  i := 0;
  while (not ZTable1.Eof) do
  begin
    cbKabkot.Items.Add(ZTable1.FieldByName('TAMPIL').AsString);
    if (ikabkot = ZTable1.FieldByName('KODE').AsString) then
      idx := i;
    ZTable1.Next;
    Inc(i);
  end;
  // ShowMessage(ikabkot);
  cbKabkot.ItemIndex := idx;
  if (skode<>'00') then cbkabkot.Enabled:=false;
  // KEC
  with ZTable1 do
  begin
    active := False;
    TableName := 'kecamatan';
    Filter := 'KODE_KABKOT=' + QuotedStr(ikabkot);
    Filtered := True;
    active := True;
    first;
  end;
  cbKec.Clear;
  i := 0;
  while (not ZTable1.Eof) do
  begin
    cbKec.Items.Add(ZTable1.FieldByName('TAMPIL').AsString);
    if (ikec = ZTable1.FieldByName('KODE_KECAMATAN').AsString) then
      idx := i;
    ZTable1.Next;
    Inc(i);
  end;
  cbKec.ItemIndex := idx;
  if(length(skode) > 2) then cbkec.Enabled:=false;
  
  // DESA
  with ZTable1 do
  begin
    active := False;
    TableName := 'desa';
    Filter := 'KODE_KECAMATAN=' + QuotedStr(ikec);
    Filtered := True;
    active := True;
    first;
  end;
  cbDesa.Clear;
  i := 0;
  while (not ZTable1.Eof) do
  begin
    cbDesa.Items.Add(ZTable1.FieldByName('TAMPIL').AsString);
    if (AnsiUpperCase(idesa) = AnsiUpperCase(ZTable1.FieldByName('KODE_DESA').AsString)) then
      idx := i;
    ZTable1.Next;
    Inc(i);
  end;
  cbDesa.ItemIndex := idx;
  
  // TPS
  with zquery1 do
  begin
    active := False;
    SQL.Clear;
    SQL.Text := 'SElECT ID_TPS, NAMA FROM daftar_tps WHERE KODE_DESA=' + QuotedStr(sdesa) + ' ORDER BY NAMA+0';
    active := True;
    first;
  end;
  cbTPS.Clear;
  i:=0;
  btnDaftar.Enabled := false;
  while (not Zquery1.Eof) do
  begin
    cbTPS.Items.Add(Zquery1.FieldByName('NAMA').AsString);
    zquery1.Next;
    inc(i);
  end;
  //
  btnDaftar.Enabled := False;
end;

procedure TForm_DaftarkanTPS.btnBatalClick(Sender: TObject);
begin
  Close;
end;

procedure TForm_DaftarkanTPS.btnDaftarClick(Sender: TObject);
var
  buttonSelected,i,jml,tbh : Integer;
  tanggal, idtgl : string;
begin
  //tanggal
      tanggal := FormatDateTime('yyyy-mm-dd hh:mm:ss', Now());
      with ZQuery1 do  //masukkin nih gan
        begin
          Active:=False;
          sql.Clear;
          sql.Text := 'INSERT INTO tanggal (TANGGAL) VALUES ("'+tanggal+'")';
          ExecSQL;
        end;
      with ZQuery1 do  //masukkin nih gan
        begin
          Active:=False;
          sql.Clear;
          sql.Text := 'SELECT * FROM tanggal ORDER BY ID_TANGGAL DESC';
          Active:=True;
          idtgl := FieldByName('ID_TANGGAL').AsString;  //dapat ID_TGL
        end;
  buttonSelected := MessageDlg('Apakah anda yakin akan menambah ke TPS ini?',mtConfirmation , mbOKCancel, 0);
  if buttonSelected = mrOK  then begin
    //prosedur memasukkan kecuali yang sudah ada TPS
    jml:=StrToInt(sjml);
    tbh:=0;
    for i:=0 to vpem.Count-1 do begin
      if (cekDaftar(vpem[i])) and (((jml<600) and (copy(cbTps.Items[cbTps.ItemIndex],1,1) <> 'K')) or (copy(cbTps.Items[cbTps.ItemIndex],1,1) = 'K')) then begin
        inc(jml);
        inc(tbh);
        with ZQuery1 do begin
          Active:=False;
          sql.Clear;
          sql.Text:='UPDATE pemilih_'+sdesaasal+' SET TPS='+stps+', ID_TANGGAL = '+idtgl+' WHERE ID_USER='+vpem[i];
          ExecSQL;
        end;
      end;
    end;
    ShowMessage('Berhasil mendaftarkan '+IntToStr(tbh)+' orang ke TPS '+cbTPS.Items[cbTPS.ItemIndex]);
    Close;
  end;
end;

function TForm_DaftarkanTPS.cekDaftar(id:String):Boolean;
var
  val:Boolean;
begin
  with Zquery1 do begin
    active:=false;
    sql.clear;
    sql.text:='SELECT TPS FROM pemilih_'+sdesaasal+' WHERE ID_USER='+id;
    active:=true;
    if (fields[0].asstring='0') then
      val:=True
    else
      val:=False
  end;
  Result:=val;
end;

procedure TForm_DaftarkanTPS.cbDesaChange(Sender: TObject);
var
  pilihan: String;
begin
  pilihan := cbDesa.Items[cbDesa.ItemIndex];
  with ZTable1 do
  begin
    active := False;
    TableName := 'desa';
    Filter := 'TAMPIL = ' + QuotedStr(pilihan)
      + ' and KODE_KECAMATAN = ' + QuotedStr(skec);
    Filtered := True;
    active := True;
    first;
    sdesa := FieldByName('KODE_DESA').AsString;
    //

  end;
  with zquery1 do
  begin
    active := False;
    SQL.Clear;
    SQL.Text := 'SElECT ID_TPS, NAMA FROM daftar_tps WHERE KODE_DESA=' + QuotedStr(sdesa) + ' ORDER BY NAMA+0';
    active := True;
    first;
  end;
  cbTPS.Clear;
  btnDaftar.Enabled := false;
  while (not Zquery1.Eof) do
  begin
    cbTPS.Items.Add(Zquery1.FieldByName('NAMA').AsString);
    zquery1.Next;
  end;
end;

procedure TForm_DaftarkanTPS.cbKabkotChange(Sender: TObject);
var
  pilihan: String;
begin
  pilihan := cbKabkot.Items[cbKabkot.ItemIndex];
  with ZTable1 do
  begin
    active := False;
    TableName := 'kabkot';
    Filter := 'TAMPIL = ' + QuotedStr(pilihan);
    Filtered := True;
    active := True;
    first;
    skabkot := FieldByName('KODE').AsString;
    // cb2
    active := False;
    TableName := 'kecamatan';
    Filter := 'KODE_KABKOT = ' + QuotedStr(skabkot);
    Filtered := True;
    active := True;
    first;
  end;
  cbKec.Clear;
  cbDesa.Clear;
  cbTPS.Clear;
  btnDaftar.Enabled := false;
  while (not ZTable1.Eof) do
  begin
    cbKec.Items.Add(ZTable1.FieldByName('TAMPIL').AsString);
    ZTable1.Next;
  end;
end;

procedure TForm_DaftarkanTPS.cbKecChange(Sender: TObject);
var
  pilihan: String;
begin
  pilihan := cbKec.Items[cbKec.ItemIndex];
  with ZTable1 do
  begin
    active := False;
    TableName := 'kecamatan';
    Filter := 'TAMPIL = ' + QuotedStr(pilihan)
      + ' and KODE_KABKOT = ' + QuotedStr(skabkot);
    Filtered := True;
    active := True;
    first;
    skec := FieldByName('KODE_KECAMATAN').AsString;
    // cb2
    active := False;
    TableName := 'desa';
    Filter := 'KODE_KECAMATAN = ' + QuotedStr(skec);
    Filtered := True;
    active := True;
    first;
  end;
  cbDesa.Clear;
  cbTPS.Clear;
  btnDaftar.Enabled := false;
  while (not ZTable1.Eof) do
  begin
    cbDesa.Items.Add(ZTable1.FieldByName('TAMPIL').AsString);
    ZTable1.Next;
  end;
end;

procedure TForm_DaftarkanTPS.cbTPSChange(Sender: TObject);
var
  pilihan: String;
  jmlint:integer;
begin
  jmlint := 0;
  pilihan := cbTPS.Items[cbTPS.ItemIndex];
  with ZTable1 do
  begin
    active := False;
    TableName := 'daftar_tps';
    Filter := 'NAMA = ' + QuotedStr(pilihan) + ' and KODE_DESA = ' + QuotedStr
      (sdesa);
    Filtered := True;
    active := True;
    first;
    stps := FieldByName('ID_TPS').AsString;
  end;
//  ShowMessage(stps);
  btnDaftar.Enabled := True;
  ZTable1.Active:=false;
  ZTable1.TableName := 'desa';
  ZTable1.filter := 'KODE_KECAMATAN = '+quotedstr(copy(sdesa,1,5));
  Ztable1.Filtered:=true;
  ZTable1.Active:=true;
  ZTable1.First;
  while(not ZTable1.Eof) do begin
    with Zquery1 do begin
      active:=false;
      sql.Clear;
      sql.Text:='SELECT COUNT(*) FROM pemilih_'+ztable1.fieldbyname('KODE_DESA').AsString+' WHERE TPS='+stps+' AND STATUS2=''0''';
      active:=true;
      jmlint:=jmlint+Fields[0].AsInteger;
    end;
  Ztable1.Next;
  end;
  sjml:=inttostr(jmlint);
  lblJml.Caption:=sjml+' orang';
end;

end.
